CREATE PROCEDURAL LANGUAGE plpgsql;

-- DROP FUNCTION eliminar_temporales();

CREATE OR REPLACE FUNCTION eliminar_temporales()
  RETURNS bigint AS
$BODY$
DECLARE
	registro RECORD;
	resultado int8:=0;
	fecha_now date;
	hora_now time;
BEGIN
	fecha_now:=current_date;
	hora_now:=current_time;
	FOR registro IN SELECT * FROM actuacionesusuarios LOOP
		IF(fecha_now>=registro.fecha_expiracion AND hora_now >=registro.hora_expiracion) THEN
			DELETE FROM actuacionesusuarios WHERE id=registro.id;
			resultado:=resultado+1;
		END IF;
	END LOOP;
	RETURN resultado;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION eliminar_temporales() OWNER TO postgres;


-- DROP TABLE adscripciones;

CREATE TABLE adscripciones
(
  id bigserial NOT NULL,
  nombre_adscripcion character varying(255) NOT NULL,
  CONSTRAINT adscripcion_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adscripciones OWNER TO postgres;


-- DROP TABLE alcances;

CREATE TABLE alcances
(
  id bigserial NOT NULL,
  descripcion character varying NOT NULL,
  CONSTRAINT alcances_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE alcances OWNER TO postgres;



-- DROP TABLE clasificaciones;

CREATE TABLE clasificaciones
(
  id bigserial NOT NULL,
  descripcion character varying,
  CONSTRAINT clasificacion_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE clasificaciones OWNER TO postgres;


-- DROP TABLE correlativos;

CREATE TABLE correlativos
(
  id bigserial NOT NULL,
  tipo character varying(255) NOT NULL,
  anio bigint NOT NULL,
  ultimo bigint NOT NULL,
  id_actividad bigint,
  CONSTRAINT correlativos_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE correlativos OWNER TO postgres;


-- DROP TABLE dependencias;

CREATE TABLE dependencias
(
  id bigserial NOT NULL,
  nombre_dependencia character varying(255) NOT NULL,
  CONSTRAINT dependencias_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE dependencias OWNER TO postgres;

-- DROP TABLE entidades;

CREATE TABLE entidades
(
  id bigserial NOT NULL,
  descripcion character varying NOT NULL,
  CONSTRAINT entidades_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE entidades OWNER TO postgres;


-- DROP TABLE estatus;

CREATE TABLE estatus
(
  id bigserial NOT NULL,
  descripcion character varying NOT NULL,
  CONSTRAINT estatus_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE estatus OWNER TO postgres;


-- DROP TABLE organismos;

CREATE TABLE organismos
(
  id bigserial NOT NULL,
  nombre_organismo character varying(255) NOT NULL,
  CONSTRAINT organismo_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE organismos OWNER TO postgres;


-- DROP TABLE paises;

CREATE TABLE paises
(
  id bigserial NOT NULL,
  nombre_pais character varying(255) NOT NULL,
  codigo_pais character varying(5)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE paises OWNER TO postgres;


-- DROP TABLE tematicas;

CREATE TABLE tematicas
(
  id bigserial NOT NULL,
  descripcion character varying NOT NULL,
  CONSTRAINT tematicas_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tematicas OWNER TO postgres;

-- DROP TABLE tipos;

CREATE TABLE tipos
(
  id bigserial NOT NULL,
  descripcion character varying NOT NULL,
  ind_actividad integer,
  CONSTRAINT tipos_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tipos OWNER TO postgres;

-- DROP TABLE tiposfuncionarios;

CREATE TABLE tiposfuncionarios
(
  id bigserial NOT NULL,
  tipo_funcionario character varying(255) NOT NULL,
  id_organismo bigint,
  CONSTRAINT tipo_funcionario_pk PRIMARY KEY (id),
  CONSTRAINT funcionarios_fk_organismos FOREIGN KEY (id_organismo)
      REFERENCES organismos (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tiposfuncionarios OWNER TO postgres;


-- DROP TABLE personas;

CREATE TABLE personas
(
  id bigserial NOT NULL,
  tipo_identificacion character varying(1) NOT NULL,
  numero_identificacion character varying(20) NOT NULL,
  nombres character varying(100) NOT NULL,
  apellidos character varying(100) NOT NULL,
  edad integer,
  genero character varying(1),
  email character varying(100),
  estado_civil character varying(1),
  telefono_hab character varying(11),
  telefono_cel character varying(11),
  id_organismo bigint NOT NULL DEFAULT 0,
  id_adscripcion bigint NOT NULL DEFAULT 0,
  id_dependencia bigint NOT NULL DEFAULT 0,
  cargo character varying(255),
  id_tipo_funcionario bigint NOT NULL DEFAULT 0,
  id_entidad_federal bigint NOT NULL DEFAULT 0,
  id_pais bigint NOT NULL DEFAULT 230,
  fecha_creacion timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT persona_pkey PRIMARY KEY (id),
  CONSTRAINT identificacion_unique UNIQUE (numero_identificacion)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE personas OWNER TO postgres;


-- DROP TABLE usuarios;

CREATE TABLE usuarios
(
  id bigserial NOT NULL,
  alias character varying(100),
  clave character varying(50) NOT NULL,
  estatus integer NOT NULL,
  id_nivel_usuario integer NOT NULL,
  id_persona integer,
  fecha_creacion timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT usuarios_pkey PRIMARY KEY (id),
  CONSTRAINT personas_fkey FOREIGN KEY (id_persona)
      REFERENCES personas (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_alias UNIQUE (alias)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE usuarios OWNER TO postgres;


-- DROP TABLE actividades;

CREATE TABLE actividades
(
  id bigserial NOT NULL,
  codigo character varying(10) NOT NULL,
  nombre character varying(255) NOT NULL,
  anio bigint NOT NULL,
  id_clasificacion bigint NOT NULL,
  id_tematica bigint NOT NULL,
  id_alcance bigint NOT NULL,
  id_tipo_actividad bigint NOT NULL,
  convenio character varying(2),
  institucion text,
  certificacion character varying(2),
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  fecha_actualizacion date,
  CONSTRAINT actividades_pk PRIMARY KEY (id),
  CONSTRAINT actividades_fk_alcances FOREIGN KEY (id_alcance)
      REFERENCES alcances (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actividades_fk_clasificaciones FOREIGN KEY (id_clasificacion)
      REFERENCES clasificaciones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actividades_fk_tematicas FOREIGN KEY (id_tematica)
      REFERENCES tematicas (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actividades_fk_tipos FOREIGN KEY (id_tipo_actividad)
      REFERENCES tipos (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actividades_uq_codigo UNIQUE (codigo, anio)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actividades OWNER TO postgres;


-- DROP TABLE actividadesusuarios;

CREATE TABLE actividadesusuarios
(
  id bigserial NOT NULL,
  id_actividad bigint NOT NULL,
  id_usuario bigint NOT NULL,
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  fecha_actualizacion date,
  tipo_planificador bigint,
  CONSTRAINT actividadesusuarios_pk PRIMARY KEY (id),
  CONSTRAINT actividades_fk_usuarios FOREIGN KEY (id_usuario)
      REFERENCES usuarios (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actividadesusuarios_fk_actividad FOREIGN KEY (id_actividad)
      REFERENCES actividades (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actividadesusuarios OWNER TO postgres;


-- DROP TABLE actuaciones;

CREATE TABLE actuaciones
(
  id bigserial NOT NULL,
  codigo_actuacion character varying(10) NOT NULL,
  anio bigint NOT NULL,
  fecha_inicio date,
  fecha_fin date,
  id_estatus bigint NOT NULL,
  id_entidad bigint,
  id_actividad bigint NOT NULL,
  ind_financiero character varying(10) NOT NULL,
  refrigerios bigint,
  viaticos character varying(10),
  duracion double precision NOT NULL,
  horas double precision,
  lugar text,
  observaciones text,
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  fecha_actualizacion date,
  tip_financiero character varying NOT NULL,
  CONSTRAINT actuaciones_pk PRIMARY KEY (id),
  CONSTRAINT actuaciones_fk_actividades FOREIGN KEY (id_actividad)
      REFERENCES actividades (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actuaciones_fk_entidades FOREIGN KEY (id_entidad)
      REFERENCES entidades (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actuaciones_fk_estatus FOREIGN KEY (id_estatus)
      REFERENCES estatus (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actuaciones OWNER TO postgres;


-- DROP TABLE actuacionesusuarios;

CREATE TABLE actuacionesusuarios
(
  id bigserial NOT NULL,
  id_actuacion bigint NOT NULL,
  id_usuario bigint NOT NULL,
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  fecha_actualizacion date,
  fecha_expiracion date,
  hora_expiracion time without time zone,
  CONSTRAINT actuacuionesusuarios_pk PRIMARY KEY (id),
  CONSTRAINT actuaciones_fk_usuarios FOREIGN KEY (id_usuario)
      REFERENCES usuarios (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT actuacionesusuarios_fk_actuacion FOREIGN KEY (id_actuacion)
      REFERENCES actuaciones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actuacionesusuarios OWNER TO postgres;


-- DROP TABLE actuacionesparticipantes;

CREATE TABLE actuacionesparticipantes
(
  id bigserial NOT NULL,
  id_actuacion bigint NOT NULL,
  id_persona bigint NOT NULL,
  id_organismo bigint,
  id_adscripcion bigint,
  id_dependencia bigint,
  cargo character varying(255),
  id_tipo_funcionario bigint,
  id_entidad bigint,
  id_pais bigint,
  asistencia text,
  certf_asistencia integer,
  certf_aprobacion integer,
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT actuacion_participante_pk PRIMARY KEY (id),
  CONSTRAINT act_fk_actuacion FOREIGN KEY (id_actuacion)
      REFERENCES actuaciones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT act_fk_personas FOREIGN KEY (id_persona)
      REFERENCES personas (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT participante_uq UNIQUE (id_actuacion, id_persona)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actuacionesparticipantes OWNER TO postgres;


CREATE TABLE actuacionesponentes
(
  id bigserial NOT NULL,
  id_actuacion bigint NOT NULL,
  id_persona bigint NOT NULL,
  id_tratamiento bigint,
  id_organismo bigint,
  id_adscripcion bigint,
  id_dependencia bigint,
  cargo character varying(255),
  id_entidad bigint,
  id_pais bigint,
  ponencia character varying(255),
  horas double precision NOT NULL,
  evaluacion double precision,
  fecha_registro timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT actuacion_ponente_pk PRIMARY KEY (id),
  CONSTRAINT actponente_fk_actuacion FOREIGN KEY (id_actuacion)
      REFERENCES actuaciones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT act_fk_personas FOREIGN KEY (id_persona)
      REFERENCES personas (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ponente_uq UNIQUE (id_actuacion, id_persona)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actuacionesponentes OWNER TO postgres;

-- DROP TABLE tratamientos;

CREATE TABLE tratamientos
(
  id bigserial NOT NULL,
  nombre_tratamiento character varying(255) NOT NULL,
  CONSTRAINT tratamiento_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tratamientos OWNER TO postgres;
